﻿using Newtonsoft.Json;
using ServiceStack.Redis;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace Server.Util
{
    public class DBUtil
    {
        private string ConnStr;
        private SqlConnection MyConn;
        public string DBServer = "192.168.1.238";
        public string DBName = "TaxControlCore";
        public string DBUser = "sa";
        public string DBPass = "fJAq+70g";
        public string CachePrefix = "NetInvoice";
        private void OpenDB()
        {
            try
            {
                if (null == ConnStr)
                {
                    ConnStr = string.Format("Application Name=hzplatform;Server={0};Database={1};Integrated Security=False;User ID={2};Password={3};Min Pool Size=10;Max Pool Size=8192;Connect Timeout=30;Load Balance Timeout=60", DBServer, DBName, DBUser, DBPass);
                }
                if (null == MyConn)
                {
                    MyConn = new SqlConnection(ConnStr);
                    MyConn.Open();
                    return;
                }
                if (MyConn.State != ConnectionState.Open)
                {
                    MyConn = new SqlConnection(ConnStr);
                    MyConn.Open();
                    return;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
        private void CloseDB()
        {
            try
            {
                MyConn.Close();
                MyConn.Dispose();
                MyConn = null;
            }
            catch (Exception)
            {
            }
        }
        public DataTable Query(string sqlstr)
        {
            try
            {
                if (string.IsNullOrEmpty(sqlstr))
                {
                    throw new Exception("命令不能为空！");
                }
                OpenDB();
                SqlDataAdapter da = new SqlDataAdapter(sqlstr, MyConn);
                DataTable dt = new DataTable();
                da.Fill(dt);
                CloseDB();
                return dt;
            }
            catch (Exception)
            {
                CloseDB();
                throw;
            }
        }
        public DataTable Query(string sqlstr, SqlParameter[] op)
        {
            try
            {
                if (string.IsNullOrEmpty(sqlstr))
                {
                    throw new Exception("命令不能为空！");
                }
                if (null == op)
                {
                    throw new Exception("参数不能为空！");
                }
                if (op.Length < 1)
                {
                    throw new Exception("参数不能为空！");
                }
                OpenDB();
                SqlDataAdapter da = new SqlDataAdapter(sqlstr, MyConn);
                da.SelectCommand.Parameters.AddRange(op);
                DataTable dt = new DataTable();
                da.Fill(dt);
                da.SelectCommand.Parameters.Clear();
                da.Dispose();
                da = null;
                CloseDB();
                return dt;
            }
            catch (Exception)
            {
                CloseDB();
                throw;
            }
        }
        public void Exec(string sqlstr)
        {
            try
            {
                if (string.IsNullOrEmpty(sqlstr))
                {
                    throw new Exception("命令不能为空！");
                }
                OpenDB();
                SqlCommand cmd = new SqlCommand(sqlstr, MyConn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                cmd = null;
                CloseDB();
            }
            catch (Exception)
            {
                CloseDB();
                throw;
            }
        }
        public void Exec(string sqlstr, SqlParameter[] op)
        {
            try
            {
                if (string.IsNullOrEmpty(sqlstr))
                    throw new Exception("命令不能为空！");
                if (null == op)
                    throw new Exception("参数不能为空！");
                OpenDB();
                SqlCommand cmd = new SqlCommand(sqlstr, MyConn);
                cmd.Parameters.AddRange(op);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                cmd.Dispose();
                cmd = null;
                CloseDB();
            }
            catch (Exception)
            {
                CloseDB();
                throw;
            }
        }
        public void ExecTrans(string sqlstr)
        {
            try
            {
                if (string.IsNullOrEmpty(sqlstr))
                    throw new Exception("命令不能为空！");
                OpenDB();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = MyConn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = sqlstr;
                cmd.ExecuteNonQuery();
                //cmd.Transaction.Commit();
                cmd.Dispose();
                cmd = null;
                CloseDB();
            }
            catch (Exception)
            {
                CloseDB();
                throw;
            }
        }
        public void ExecTrans(string sqlstr, SqlParameter[] op)
        {
            try
            {
                if (string.IsNullOrEmpty(sqlstr))
                    throw new Exception("命令不能为空！");
                if (null == op)
                    throw new Exception("参数不能为空！");
                if (op.Length < 1)
                    throw new Exception("参数不能为空！");
                OpenDB();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = MyConn;
                cmd.CommandText = sqlstr;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddRange(op);
                cmd.ExecuteNonQuery();
                //cmd.Transaction.Commit();
                cmd.Dispose();
                cmd = null;
                CloseDB();
            }
            catch (Exception)
            {
                CloseDB();
                throw;
            }
        }
        public DataTable QueryTrans(string sqlstr)
        {
            try
            {
                if (string.IsNullOrEmpty(sqlstr))
                    throw new Exception("命令不能为空！");
                OpenDB();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = sqlstr;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = MyConn;
                cmd.ExecuteNonQuery();
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                da.Dispose();
                da = null;
                cmd.Dispose();
                cmd = null;
                CloseDB();
                return dt;
            }
            catch (Exception)
            {
                CloseDB();
                throw;
            }
        }
        public DataTable QueryTrans(string sqlstr, SqlParameter[] op)
        {
            try
            {
                if (string.IsNullOrEmpty(sqlstr))
                    throw new Exception("命令不能为空！");
                if (null == op)
                    throw new Exception("参数不能为空！");
                if (op.Length < 1)
                    throw new Exception("参数不能为空！");
                OpenDB();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = sqlstr;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddRange(op);
                cmd.Connection = MyConn;
                cmd.ExecuteNonQuery();
                DataTable dt = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(dt);
                da.Dispose();
                da = null;
                cmd.Dispose();
                cmd = null;
                CloseDB();
                return dt;
            }
            catch (Exception)
            {
                CloseDB();
                throw;
            }
        }
        public void ExecBatchInOneSqlstrTrans(string sqlstr, List<SqlParameter[]> pList)
        {
            try
            {
                if (string.IsNullOrEmpty(sqlstr))
                    throw new Exception("命令不能为空！");
                if (null == pList || pList.Count < 1)
                    throw new Exception("参数不能为空！");
                OpenDB();
                SqlCommand cmd = new SqlCommand(sqlstr, MyConn);
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sqlstr;
                cmd.Transaction = MyConn.BeginTransaction();
                for (int i = 0; i < pList.Count; i++)
                {
                    try
                    {
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddRange(pList[i]);
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception)
                    {
                        cmd.Transaction.Rollback();
                        throw;
                    }
                }
                cmd.Transaction.Commit();
                CloseDB();
            }
            catch (Exception)
            {
                CloseDB();
                throw;
            }
        }
    }
}
